package com.hnkywl.sts.dao.report;

import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 销售合同履约状况
 * Created by ccr on 2014/6/24.
 */
@Repository
public class XshtlyzkDao extends SimpleHibernateTemplate{
    public Page<Map> xshtList(String cght,String xsht, String xsdh,String wzbm,Long khId,int pageNo,int pageSize){
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        if (StringUtils.isNotEmpty(cght)) {
            condition.add(" cght.htbm like :cght ");
            param.put("cght", cght + "%");
        }
        if (StringUtils.isNotEmpty(xsht)) {
            condition.add(" xsht.htbm like :xsht ");
            param.put("xsht", xsht + "%");
        }
        if (StringUtils.isNotEmpty(xsdh)) {
            condition.add(" t.xsdh like :xsdh ");
            param.put("xsdh", xsdh + "%");
        }
        if (StringUtils.isNotEmpty(wzbm)) {
            condition.add(" gg.code like :code ");
            param.put("code", wzbm + "%");
        }
        if (khId != null) {
            condition.add(" xsht.hzf_id = :khId ");
            param.put("khId", khId);
        }
        String querySql = "select cght.htbm cght,xsht.htbm xsht,xsht.id id,trunc(xsht.jsrq)-trunc(xsht.qdrq) htq," +
                "(select sum(x.sbsl) from t_biz_htmx x where x.ht_id = xsht.id) htsl," +
                "sum(nvl(t.xssl,0)) yxsl,sum(case when t.shzt = 1 then t.xssl else 0 end) kpsl " +
                "from t_biz_ht xsht " +
                "left join t_biz_ht cght on xsht.zddhcght_id = cght.id and cght.deleted != 1 " +
                "left join t_biz_htmx htmx on htmx.ht_id = xsht.id and htmx.deleted != 1 " +
                "left join t_sys_gg gg on htmx.wz_id = gg.id " +
                "left join (select xsd.xsdh, mx.ck_sbsl xssl,cgd.cght_id,kh.sbgs_id,kh.khbm,jsd.shzt from t_biz_xsmx mx " +
                "left join t_biz_xsd xsd on xsd.id = mx.xsd_id and xsd.deleted != 1 " +
                "left join t_biz_kh kh on mx.kh_id = kh.id and kh.deleted != 1 " +
                "left join t_biz_cgd cgd on kh.cgd_id = cgd.id and cgd.deleted != 1 " +
                "left join t_sys_gg g on mx.jtwz_id = g.id  " +
                "left join t_biz_jsd jsd on mx.xsjsd_id = jsd.id and jsd.deleted != 1 " +
                "where xsd.yck = 1 and xsd.deleted != 1 and mx.deleted != 1) t on t.cght_id = cght.id and t.sbgs_id = xsht.hzf_id " +
                "where xsht.zddhcght_id is not null and cght.deleted != 1 and xsht.deleted != 1 and xsht.type = 'xsht' " +
                (!condition.isEmpty() ? ("and" + StringUtils.join(condition, " and "))  : "") +
                "group by xsht.id,cght.htbm,xsht.htbm,xsht.jsrq,xsht.qdrq,cght.id,xsht.hzf_id";

        String countSql = "select count(1) from (" + querySql + ")";
        String resultSql = "select s.id \"id\", s.cght \"cght\",s.xsht \"xsht\",s.htq \"htq\",s.htsl \"htsl\",s.yxsl \"yxsl\",s.htsl-s.yxsl \"wxsl\", s.htsl-s.kpsl \"wkpsl\" from (" +
                querySql + ") s order by s.cght,s.xsht";
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    public Map xshtlyzkDetailList(Long htId) {
        String resultSql = "select xsd.xsdh \"xsdh\",g.code \"gg.code\",g.name \"gg.name\",g.ggxh \"gg.ggxh\",mx.ck_sbsl \"cksl\",mx.hsj \"shj\",jsd.shzt \"kpzt\" ";
        String footerSql = "select sum(mx.ck_sbsl) \"cksl\"";
        String from = "from t_biz_xsmx mx " +
                "left join t_biz_xsd xsd on xsd.id = mx.xsd_id and xsd.deleted = 0 " +
                "left join t_sys_gg g on g.id = mx.jtwz_id " +
                "left join t_biz_kh kh on mx.kh_id = kh.id and kh.deleted = 0 " +
                "left join t_biz_jsd jsd on mx.xsjsd_id = jsd.id and jsd.deleted != 1 " +
                "left join (select a.id xshtid, a.zddhcght_id, a.hzf_id,c.id cgdid from t_biz_ht a, t_biz_ht b, t_biz_cgd c " +
                " where a.zddhcght_id = b.id and b.id = c.cght_id and a.type = 'xsht' and b.type = 'cght') t  " +
                " on t.cgdid = kh.cgd_id and t.hzf_id = kh.sbgs_id " +
                "where xsd.yck = 1 and mx.xsd_id is not null and xsd.deleted != 1 and kh.deleted != 1 " +
                "   and mx.deleted != 1 and t.zddhcght_id is not null and t.xshtid = :htId";
        List<Map> result = createSQLQuery(resultSql + from)
                .setParameter("htId", htId)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        List<Map> footer = createSQLQuery(footerSql + from)
                .setParameter("htId",htId)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        Map<String,List> map = new HashMap<String, List>();
        map.put("rows",result);
        map.put("footer",footer);
        return map;
    }
}
